<?php
namespace app\index\model;

use app\common\model\RemoteModel;
use app\index\model\LRegister;
use app\index\model\Retention;
use app\ux\UtilArr;
use think\Config;
use think\Db;

class LLogin extends RemoteModel
{

    protected $connection = 'mysql://root:JB6MUn64xICo@rm-bp1h205f36fv8pecto.mysql.rds.aliyuncs.com:3306/dragon#utf8';
    protected $url = "http://115.239.196.59:19998/?sid=144169&mid=20005&serverid=1&cid=1&tablename=l_login";
    
    //重写父类初始化方法
    //
    protected function initialize()
    {
        parent::initialize();

    }

    /**
     * 获取日活跃人数
     *
     * @param    string $date 计算日期
     * @return   int DAU活跃人数
     */
    public function getDAU($date, $by_device = false)
    {
        try {

            //判断是否根据设备计算留存
            //
            if (false == $by_device) {
                $res = Db::query("SELECT COUNT(DISTINCT `characterid`) AS `count` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$date}') = 0 AND `status` in(1,2) LIMIT 1");
                $rtn = $res[0]['count'];

                return $rtn;

            } else if (true == $by_device) {

                return $this->getDAUByDevice($date);

            }
        } catch (\Exception $e) {
            throw new \think\Exception('异常消息:' . $e->getMessage());
        }
    }

    /**
     * 获取所有非空设备数量
     * @DateTime 2018-02-07
     * @return   [type]
     */
    public function getTotalDeviceWithoutEmptyDevice($date)
    {

        $sql = "SELECT DISTINCT `device` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$date}') = 0 AND `device` IS NOT null AND TRIM(`device`) != '' AND `status` in(1,2)";

        $res     = Db::query($sql);
        $devices = array_column($res, 'device');

        $count = 0;

        $rtn = [];
        foreach ($devices as $v) {

            $sql_cid = "SELECT DISTINCT `characterid` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$date}') = 0 AND (`device`  = '{$v}') AND `status` in(1,2)";
            $res     = Db::query($sql_cid);
            $cid_arr = array_column($res, 'characterid');

            foreach ($cid_arr as $key => $cid) {
                if (in_array($cid, $rtn)) {
                    unset($cid_arr[$key]);
                }
            }
            if (count($cid_arr) == 0) {
                $add = 0;
            } else {
                $add = 1;
            }
            $count += $add;
            $rtn = UtilArr::arrayMergeWithDistinct($rtn, $cid_arr);
        }
        return $count;
    }
    /**
     * 获取所有非空设备上不重复的角色ID
     * @DateTime 2018-02-07
     * @return   [type]
     */
    public function getCharacteridWithoutEmptyDevice($date)
    {

        $sql     = "SELECT DISTINCT `characterid` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$date}') = 0 AND `device` IS NOT null AND TRIM(`device`) != '' AND `status` in(1,2)";
        $res     = Db::query($sql);
        $cid_arr = array_column($res, 'characterid');

        return $cid_arr;
    }
    /**
     * 获取所有非空设备上不重复的角色ID
     * @DateTime 2018-02-07
     * @return   [type]
     */
    public function getCharacteridWithEmptyDevice($date)
    {

        $sql     = "SELECT DISTINCT `characterid` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$date}') = 0 AND (`device` IS null OR TRIM(`device`) = '') AND `status` in(1,2)";
        $res     = Db::query($sql);
        $cid_arr = array_column($res, 'characterid');

        return $cid_arr;
    }
    /**
     * 删除空设备重复角色ID
     * @DateTime 2018-02-07
     * @param    [type] $date [description]
     * @return   [type]
     */
    public function getTotalDistinctCidWithEmptyDevice($date)
    {

        $empty_device_cids = $this->getCharacteridWithEmptyDevice($date);
        $device_cids       = $this->getCharacteridWithoutEmptyDevice($date);

        foreach ($empty_device_cids as $k => $v) {
            foreach ($device_cids as $v1) {
                if ($v == $v1) {
                    unset($empty_device_cids[$k]); //删除数组同值元素
                }
            }
        }

        return $empty_device_cids;
    }
    /**
     * 根据设备获取留存计算SQL
     * @DateTime 2018-02-07
     * @return   [type]
     */
    public function getDAUByDevice($date)
    {

        $dau_empty  = count($this->getTotalDistinctCidWithEmptyDevice($date));
        $dau_device = $this->getTotalDeviceWithoutEmptyDevice($date);
        $total      = $dau_device + $dau_empty;

        return ($total);
    }
    /**
     * 留存率
     *
     * @param    string $reg_date 注册日期
     * @param    string $calc_date 计算日期
     * @return   float 留存率
     */
    public function getRET($reg_date, $calc_date, $by_device = false)
    {
        //try {

        $LRegister = new LRegister();

        //按照日期获取留存记录
        //
        $rec = Retention::getByDate($reg_date);

        //注册人数
        //
        $reg_num = $rec ? $rec['register'] : $LRegister->getRegTotal($reg_date);

        //注册人数大于0判断

        if ($reg_num > 0) {

            //留存人数
            //
            $reg_ids = $LRegister->getRegCid($reg_date);
            $str_ids = join(",", $reg_ids);

            //判断是否根据设备计算留存
            //
            //if ($by_device) {

            // return $this->getRetbyTotalDevice($calc_date, $str_ids);

            //} else if (false == $by_device) {

            $sql     = "SELECT COUNT(DISTINCT `characterid`) AS `count` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$calc_date}') = 0 AND `status` in(1,2) AND `characterid` IN ({$str_ids}) LIMIT 1";
            $res     = Db::query($sql);
            $ret_num = $res[0]['count'];

            return $ret_num;
            //}

        } else {

            //注册人数为0时，留存人数为0
            //
            return 0;
        }

        //} catch (\Exception $e) {
        //    throw new \think\Exception('异常消息:' . $e->getMessage());
        //}
    }
    /**
     * 获取总留存人数根据所有设备
     * @DateTime 2018-02-07
     * @param    [type] $calc_date [description]
     * @param    [type] $str_ids [description]
     * @return   [type]
     */
    public function getRetbyTotalDevice($calc_date, $str_ids)
    {

        return ($this->getRetbyDevice($calc_date, $str_ids) + $this->getRetByNullDevice($calc_date, $str_ids));
    }
    /**
     * 根据设备获取留存计算SQL
     * @DateTime 2018-02-07
     * @return   [type]
     */
    public function getRetbyDevice($calc_date, $str_ids)
    {
        $subsql  = "(SELECT COUNT(DISTINCT `characterid`) AS `count` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$calc_date}') = 0 AND `status` in(1,2) AND `characterid` IN ({$str_ids}) AND `device` IS NOT null AND TRIM(`device`) != '' GROUP BY `device`) t";
        $sql     = "SELECT COUNT(t.count) AS count FROM {$subsql}  LIMIT 1";
        $res     = Db::query($sql);
        $ret_num = $res[0]['count'];

        return $ret_num;
    }

    /**
     * 根据设备获取留存计算SQL
     * @DateTime 2018-02-07
     * @return   [type]
     */
    public function getRetByNullDevice($calc_date, $str_ids)
    {
        $sql     = "SELECT COUNT(DISTINCT `characterid`) AS `count` FROM `l_login` WHERE DATEDIFF(`createtime`,'{$calc_date}') = 0 AND `status` in(1,2) AND `characterid` IN ({$str_ids}) AND `device` IS null";
        $res     = Db::query($sql);
        $ret_num = $res[0]['count'];

        return $ret_num;
    }
}
